{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取工作簿和工作表\n",
    "path=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\98.账务调整\\应收账款调整至预收账款\\应收账款科目_辅助余额表.xlsx'\n",
    "wb=xw.Book(path)\n",
    "sheet=wb.sheets('Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取数据到df，用pandas进行数据处理\n",
    "df=sheet.range('a1').expand('table').options(pd.DataFrame).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 往来科目调整(df1):\n",
    "    \"\"\"\n",
    "    1. 从df1中读取往来科目调整数据\n",
    "    2. 生成调整后的数据\n",
    "    3. 保存调整后的数据\n",
    "    \"\"\"\n",
    "    df1=df1.reset_index(drop=True) #保证index从0开始\n",
    "    摘要=df1.loc[0,'new摘要']\n",
    "    金额=df1.loc[0,'金额']\n",
    "    往来单位编码=df1.loc[0,'往来单位编码']\n",
    "    往来单位名称=df1.loc[0,'往来单位名称']\n",
    "    \n",
    "    # 应收账款行的数据\n",
    "    data={\n",
    "    '摘要':摘要,\n",
    "    '科目编码':'1122', #应收账款科目\n",
    "    '科目':'应收账款',\n",
    "    '币种':'人民币',\n",
    "    '借贷方向':'借方',\n",
    "    '原币':金额*-1,  #红冲应收账款\n",
    "    '本币':金额*-1,  #红冲应收账款\n",
    "    '往来单位编码':往来单位编码,\n",
    "    '往来单位':往来单位名称\n",
    "    }\n",
    "    应收df=pd.DataFrame(data,index=[0])\n",
    "    # 预收账款行的数据\n",
    "    data={\n",
    "    '摘要':摘要,\n",
    "    '科目编码':'2203', #应收账款科目\n",
    "    '科目':'预收账款',\n",
    "    '币种':'人民币',\n",
    "    '借贷方向':'借方',\n",
    "    '原币':金额,\n",
    "    '本币':金额,\n",
    "    '往来单位编码':往来单位编码,\n",
    "    '往来单位':往来单位名称\n",
    "    }\n",
    "    预收df=pd.DataFrame(data,index=[1])\n",
    "\n",
    "    合并=pd.concat([应收df,预收df])\n",
    "    return 合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证导入导出=df.groupby(by=['分组标识']).apply(往来科目调整)  #整理完成的数据，可以向凭证导入导出中写入了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从凭证导入导出将数据写入到《凭证导入导出》\n",
    "凭证模版=wb.sheets('凭证导入导出')\n",
    "\n",
    "凭证模版.range('a4:ab10000').clear_contents()\n",
    "凭证模版.range('h2:j2').value=凭证导入导出.loc[:,['科目编码','科目','币种']].values  \n",
    "凭证模版.range('m2:o2').value=凭证导入导出.loc[:,['借贷方向','原币','本币']].values\n",
    "凭证模版.range('y2:ab2').value=凭证导入导出.loc[:,['往来单位编码','往来单位']].values\n",
    "凭证模版.range('g2').options(transpose=True).value= 凭证导入导出.loc[:,'摘要'].values #摘要\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
